package com.kx.dao;

import com.kx.entity.City;
import com.kx.entity.Province;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

//使用jdbc访问数据库
public class QueryDao {

    private Connection conn = null;
    private PreparedStatement pst = null;
    private ResultSet rs = null;
    private String sql = "";
    private String url = "jdbc:mysql://localhost:3306/ajaxjquery";
    private String username = "root";
    private String password = "123456";

    //查询所有的省份信息
    public List<Province> queryProvinceList() {
        List<Province> provinces = new ArrayList<>();
        Province p = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, username, password);
            sql = "select id,name,jiancheng,shenghui from province order by id";
            pst = conn.prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                p = new Province();
                p.setId(rs.getInt("id"));
                p.setName(rs.getString("name"));
                p.setJiancheng(rs.getString("jiancheng"));
                p.setShenghui(rs.getString("shenghui"));
                provinces.add(p);
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return provinces;
    }
    //查询一个省份下面的所有城市
    public List<City> queryCityList(Integer provinceid) {
        List<City> cities = new ArrayList<>();

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, username, password);
            sql = "select id,name from city where provinceid = ? ";
            pst = conn.prepareStatement(sql);
            pst.setInt(1,provinceid);
            rs = pst.executeQuery();
            City c = null;
            while (rs.next()) {
                c = new City();
                c.setId(rs.getInt("id"));
                c.setName(rs.getString("name"));
                cities.add(c);
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return cities;
    }


    //根据id获取名称
    public String queryProvinceNameById(Integer provinceId) {

        String name = "";
        //加载驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, username, password);

            sql = "select name from province where id=? ";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, provinceId);
            rs = pst.executeQuery();

//            while (rs.next()){
//                name=rs.getString("name");
//            }
            if (rs.next()) {
                name = rs.getString("name");
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return name;
    }

    //根据id获取Province对象
    public Province queryProvinceById(Integer provinceId) {

        Province province = null;
        //加载驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, username, password);

            sql = "select id,name,jiancheng,shenghui from province where id=? ";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, provinceId);
            rs = pst.executeQuery();

//            while (rs.next()){
//                name=rs.getString("name");
//            }
            if (rs.next()) {
                province = new Province();
                province.setId(rs.getInt("id"));
                province.setName(rs.getString("name"));
                province.setJiancheng(rs.getString("jiancheng"));
                province.setShenghui(rs.getString("shenghui"));
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return province;
    }
}
